﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using E.Model;
using X.DALSqlServer;

namespace E.DAL
{
    public class UserDAL
    {
        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ShortUserModel Login(UserModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@LoginID", model.LoginID),
                new SqlParameter("@Pwd", model.Pwd),
                new SqlParameter("@LastLoginTime", model.LastLoginTime),
                new SqlParameter("@LastLoginIP", model.LastLoginIP)
            };
            ShortUserModel sum = new ShortUserModel();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_login", parms))
            {
                if (dr.Read())
                {
                    sum.ID = (int)dr["ID"];
                    sum.UserName = dr["UserName"].ToString();
                    sum.AreaID = (int)dr["AreaID"];
                    sum.IsPublic = (bool)dr["IsPublic"];
                }
                else
                    sum.ID = -1;
                return sum;
            }
        }

        /// <summary>
        /// 向登陆历史表中加入数据
        /// </summary>
        /// <param name="model"></param>
        public void InsertLoginHistory(UserModel model)
        {
            string sql = "insert into E_LoginHistory(UserId, LoginTime) values (@UserId, @LoginTime)";
            
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserId", model.ID),
                new SqlParameter("@LoginTime", DateTime.Now)
            };

            SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public int UpdatePassword(int userid, string pwd)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", userid),
                new SqlParameter("@Pwd", pwd)
            };
            string sql = "UPDATE E_User SET Pwd = @Pwd WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 修改密码（需要原密码）
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="originalpwd"></param>
        /// <param name="newpwd"></param>
        /// <returns></returns>
        public int UpdatePasswordWithOriginalPwd(int userid, string originalpwd, string newpwd)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", userid),
                new SqlParameter("@OriginalPwd", originalpwd),
                new SqlParameter("@NewPwd", newpwd)
            };
            string sql = "UPDATE E_User SET Pwd = @NewPwd WHERE ID = @ID AND Pwd = @OriginalPwd";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 获取用户证书列表
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IList<UserCertificateLogModel> GetUserCertificateList(int userid)
        {
            IList<UserCertificateLogModel> list = new List<UserCertificateLogModel>();
            string sql = "SELECT ID,CertificateID,CertificateName,AddDate FROM v_usercertificatelog WHERE UserID = " + userid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    UserCertificateLogModel model = new UserCertificateLogModel();
                    model.ID = (int)dr["ID"];
                    model.CertificateID = (int)dr["CertificateID"];
                    model.CertificateName = dr["CertificateName"].ToString();
                    model.AddDate = (DateTime)dr["AddDate"];
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取学习成绩列表
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="courseRelationID"></param>
        /// <param name="examCategoryID"></param>
        /// <returns></returns>
        public IList<ShortUserScoreModel> GetUserScoreList(int userid, int examCategoryID)
        {
            IList<ShortUserScoreModel> list = new List<ShortUserScoreModel>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid),
                new SqlParameter("@ExamCategoryID", examCategoryID)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getUserUnfinishedLessonScore", parms))
            {
                while (dr.Read())
                {
                    ShortUserScoreModel model = new ShortUserScoreModel();
                    model.ID = (int)dr["ID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.Score = (decimal)dr["Score"];
                    model.AddDate = (DateTime)dr["AddDate"];
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 检查登录ID
        /// </summary>
        /// <param name="loginid"></param>
        /// <returns></returns>
        public bool CheckExistLoginID(string loginid)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@LoginID", loginid)
            };
            string sql = "SELECT ID FROM E_User WHERE LoginID = @LoginID";
            return SqlHelper.Exists(sql, parms);
        }

        /// <summary>
        /// 检查身份证
        /// </summary>
        /// <param name="idcard"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public bool CheckExistIDCard(string idcard, int userid)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@IDCard", idcard),
                new SqlParameter("@UserID", userid)
            };
            string sql = "SELECT a.ID FROM E_UserInfo a INNER JOIN E_User b ON a.UserID = b.ID WHERE b.DelFlag = 0 AND a.IDCard = @IDCard ";
            if (userid != -1)
            {
                sql += " AND a.UserID <> @UserID ";
            }
            return SqlHelper.Exists(sql, parms);
        }

        /// <summary>
        /// 添加学员
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddUser(UserModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@LoginID", model.LoginID),
                new SqlParameter("@Pwd", model.Pwd),
                new SqlParameter("@RegisterTime", model.RegisterTime),
                new SqlParameter("@CurrentCertificateID", model.CurrentCertificateID),
                new SqlParameter("@Email", model.Email),
                new SqlParameter("@UserName", model.UserName),
                new SqlParameter("@IDCard", model.IDCard),
                new SqlParameter("@Gender", model.Gender),
                new SqlParameter("@AreaID", model.AreaID),
                new SqlParameter("@ForgetPwdQuestionID", model.ForgetPwdQuestionID),
                new SqlParameter("@ForgetPwdAnswer", model.ForgetPwdAnswer),
                new SqlParameter("@Education", model.EducationID),
                new SqlParameter("@SkillGroup", model.SkillGroupId),
                new SqlParameter("@Position", model.Position),
                new SqlParameter("@Lan", model.Lan),
                new SqlParameter("@EntryDate", model.EntryDate),
                new SqlParameter("@SeparationDate", model.SeparationDate),
                new SqlParameter("@TrainingDate", model.TrainingDate),
                new SqlParameter("@SkillCertificate", model.SkillCertificate),
                new SqlParameter("@IsPublic", model.IsPublic)
            };
            return SqlHelper.ExecuteNonQueryByProcecdure("sp_addUsers", parms);
        }

        /// <summary>
        /// 批量添加学员
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddPluralUsers(PluralUserModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Prefix", model.Prefix),
                new SqlParameter("@Pwd", model.Pwd),
                new SqlParameter("@StartNo", model.StartNo),
                new SqlParameter("@EndNo", model.EndNo),
                new SqlParameter("@Quantity", model.Quantity),
                new SqlParameter("@AreaID", model.AreaID),
                new SqlParameter("@CurrentCertificateID", model.CurrentCertificateID),
                new SqlParameter("@RegisterTime", model.RegisterTime),
                new SqlParameter("@ForgetPwdQuestionID", model.ForgetPwdQuestionID),
                new SqlParameter("@ForgetPwdAnswer", model.ForgetPwdAnswer),
                new SqlParameter("@CheckCondition", model.CheckCondition),
                new SqlParameter("@Education", model.EducationID),
                new SqlParameter("@IsPublic", model.IsPublic)
            };
            return SqlHelper.ExecuteNonQueryByProcecdure("sp_addPluralUsers", parms);
        }

        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateUserInfo(UserModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@UserName", model.UserName),
                new SqlParameter("@IDCard", model.IDCard),
                new SqlParameter("@Email", model.Email),
                new SqlParameter("@Gender", model.Gender),
                new SqlParameter("@ForgetPwdQuestionID", model.ForgetPwdQuestionID),
                new SqlParameter("@ForgetPwdAnswer", model.ForgetPwdAnswer),
                new SqlParameter("@Education", model.EducationID),
                new SqlParameter("@SkillGroup", model.SkillGroupId),
                new SqlParameter("@Position", model.Position),
                new SqlParameter("@Lan", model.Lan),
                new SqlParameter("@EntryDate", model.EntryDate),
                new SqlParameter("@SeparationDate", model.SeparationDate),
                new SqlParameter("@TrainingDate", model.TrainingDate),
                new SqlParameter("@SkillCertificate", model.SkillCertificate)
            };
            string sql = "UPDATE E_UserInfo SET Email = @Email, UserName = @UserName, IDCard = @IDCard, Gender = @Gender, Education = @Education, SkillGroup = @SkillGroup, Position = @Position, Lan = @Lan, EntryDate = @EntryDate, SeparationDate = @SeparationDate, TrainingDate = @TrainingDate, SkillCertificate = @SkillCertificate WHERE UserID = @ID;UPDATE E_User SET ForgetPwdQuestionID = @ForgetPwdQuestionID, ForgetPwdAnswer = @ForgetPwdAnswer WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public UserModel GetUserInfoByUserID(int userid)
        {
            string sql = @"SELECT     a.ID, a.LoginID, a.CurrentCertificateID, b.Email, b.UserName, b.IDCard, b.Gender, b.AreaID, b.SkillGroup, b.Position, b.Lan, b.EntryDate, b.SeparationDate, b.TrainingDate, b.SkillCertificate,
                                                 e.Name AS Education, c.Name AS AreaName, d.Name AS CertificateName, e.ID AS EducationID, a.ForgetPwdQuestionID, a.ForgetPwdAnswer, a.IsPublic
                            FROM         E_User AS a INNER JOIN
                                                  E_UserInfo AS b ON a.ID = b.UserID INNER JOIN
                                                  E_Dict_Area AS c ON b.AreaID = c.ID INNER JOIN
                                                  E_Dict_Certificate AS d ON a.CurrentCertificateID = d.ID INNER JOIN
                                                  E_Dict_Education AS e ON b.Education = e.ID
                            WHERE     (a.ID = " + userid + " )";
            UserModel model = new UserModel();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.LoginID = dr["LoginID"].ToString();
                    model.Gender = (bool)dr["Gender"];
                    model.CurrentCertificateID = (int)dr["CurrentCertificateID"];
                    model.AreaID = (int)dr["AreaID"];
                    model.Email = dr["Email"].ToString();
                    model.UserName = dr["UserName"].ToString();
                    model.IDCard = dr["IDCard"].ToString();
                    model.EducationID = (int)dr["EducationID"];
                    model.Education = dr["Education"].ToString();
                    model.AreaName = dr["AreaName"].ToString();
                    model.ForgetPwdQuestionID = (int)dr["ForgetPwdQuestionID"];
                    model.ForgetPwdAnswer = dr["ForgetPwdAnswer"].ToString();
                    model.CurrentCertificateName = dr["CertificateName"].ToString();
                    model.SkillGroupId = dr["SkillGroup"] == null ? -1 : (int)dr["SkillGroup"];
                    model.Position = dr["Position"].ToString();
                    model.Lan = dr["Lan"].ToString();
                    model.EntryDate = dr["EntryDate"].ToString();
                    model.SeparationDate = dr["SeparationDate"].ToString();
                    model.TrainingDate = dr["TrainingDate"].ToString();
                    model.SkillCertificate = dr["SkillCertificate"].ToString();
                    model.IsPublic = (bool)dr["IsPublic"];
                }
                else
                    model.ID = -1;
                return model;
            }
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <param name="model"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public ListModel GetUserListByCondition(UserModel model, int pageSize, int pageIndex)
        {
            ListModel lm = new ListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserName", model.UserName),
                new SqlParameter("@LoginID", model.LoginID),
                new SqlParameter("@AreaID", model.AreaID),
                new SqlParameter("@Email", model.Email),
                new SqlParameter("@EntryFromDate", model.EntryFromDate),
                new SqlParameter("@EntryToDate", model.EntryToDate),
                new SqlParameter("@CurrentCertificateID", model.CurrentCertificateID),
                new SqlParameter("@DelFlag", model.DelFlagInt),
                new SqlParameter("@IsPublic", model.IsPublicInt),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<UserListModel> list = new List<UserListModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getUserListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        UserListModel listModel = new UserListModel();
                        listModel.ID = (int)dr["ID"];
                        listModel.LoginID = dr["LoginID"].ToString();
                        listModel.UserName = dr["UserName"].ToString();
                        listModel.Email = dr["Email"].ToString();
                        listModel.Gender = (bool)dr["Gender"];
                        listModel.SignedCertificateName = dr["CertificateName"].ToString();
                        listModel.DelFlag = (bool)dr["DelFlag"];
                        list.Add(listModel);
                    }
                }
                lm.TotalCount = totalCount;
                lm.UserList = list;
                return lm;
            }
        }


        /// <summary>
        /// 获得没有报名制定项目和课程班级的学生
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public IList<UserListModel> GetUnSignUpInternalClassUserListByCondition(UserModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@projectId", model.CurrentCertificateID),
                new SqlParameter("@Email", model.Email),
                new SqlParameter("@LoginID", model.LoginID),
                new SqlParameter("@UserName", model.UserName),
                new SqlParameter("@AreaID", model.AreaID),
                new SqlParameter("@DelFlag", model.DelFlagInt),
                new SqlParameter("@IsPublic", model.IsPublicInt),
                new SqlParameter("@EntryFromDate", model.EntryFromDate),
                new SqlParameter("@EntryToDate", model.EntryToDate),
                new SqlParameter("@courseId", model.CourseArrayId)
                
            };
            IList<UserListModel> list = new List<UserListModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getUnSignUpInternalClassUserListByCondition", parms))
            {
                while (dr.Read())
                {
                    UserListModel listModel = new UserListModel();
                    listModel.ID = (int)dr["ID"];
                    listModel.LoginID = dr["LoginID"].ToString();
                    listModel.UserName = dr["UserName"].ToString();
                    listModel.Email = dr["Email"].ToString();
                    listModel.Gender = (bool)dr["Gender"];
                    listModel.SignedCertificateName = dr["CertificateName"].ToString();
                    listModel.DelFlag = (bool)dr["DelFlag"];
                    list.Add(listModel);
                }
                return list;
            }
        }
        /// <summary>
        /// 获得没有报名制定项目和课程班级的外训学生
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public IList<UserListModel> GetUnSignUpPublicClassUserListByCondition(UserModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@projectId", model.CurrentCertificateID),
                new SqlParameter("@Email", model.Email),
                new SqlParameter("@LoginID", model.LoginID),
                new SqlParameter("@UserName", model.UserName),
                new SqlParameter("@AreaID", model.AreaID),
                new SqlParameter("@DelFlag", model.DelFlagInt),
                new SqlParameter("@IsPublic", model.IsPublicInt),
                new SqlParameter("@EntryFromDate", model.EntryFromDate),
                new SqlParameter("@EntryToDate", model.EntryToDate),
                new SqlParameter("@courseId", model.CourseArrayId)
                
            };
            IList<UserListModel> list = new List<UserListModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getUnSignUpPublicClassUserListByCondition", parms))
            {
                while (dr.Read())
                {
                    UserListModel listModel = new UserListModel();
                    listModel.ID = (int)dr["ID"];
                    listModel.LoginID = dr["LoginID"].ToString();
                    listModel.UserName = dr["UserName"].ToString();
                    listModel.Email = dr["Email"].ToString();
                    listModel.Gender = (bool)dr["Gender"];
                    listModel.SignedCertificateName = dr["CertificateName"].ToString();
                    listModel.DelFlag = (bool)dr["DelFlag"];
                    list.Add(listModel);
                }
                return list;
            }
        }
        /// <summary>
        /// 激活用户账号
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int ActiveUser(int id)
        {
            string sql = "UPDATE E_User SET DelFlag = 0 WHERE ID = " + id;
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 冻结用户账号
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int FreezeUser(int id)
        {
            string sql = "UPDATE E_User SET DelFlag = 1 WHERE ID = " + id;
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        public int BatchFreeUser(String id)
        {
            string sql = "update E_User set DelFlag=1 where id in (" + id + ")";
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        #region 学历

        /// <summary>
        /// 学历列表
        /// </summary>
        /// <returns></returns>
        public IDictionary<int, string> GetEducationList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Dict_Education";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        #endregion

        #region 忘记密码

        /// <summary>
        /// 获取用户忘记密码信息
        /// </summary>
        /// <param name="loginid"></param>
        /// <returns></returns>
        public IDictionary<int, string> GetForgetPwdInfoByLoginID(string loginid)
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@LoginID", loginid)
            };
            string sql = "SELECT a.ID, b.Title FROM E_User AS a INNER JOIN E_Dict_PwdQuestion AS b ON a.ForgetPwdQuestionID = b.ID WHERE (a.LoginID = @LoginID)";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql, parms))
            {
                if (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取用户提示问题
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public PwdQuestionUserModel GetForgetPwdQuestionID(int userid)
        {
            PwdQuestionUserModel model = new PwdQuestionUserModel();
            string sql = "SELECT ID,ForgetPwdQuestionID,ForgetPwdAnswer FROM E_User WHERE ID = " + userid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ForgetPwdQuestionID"];
                    model.UserID = (int)dr["ID"];
                    model.Answer = dr["ForgetPwdAnswer"].ToString();
                }
                return model;
            }
        }

        /// <summary>
        /// 检查用户忘记密码提示问题答案是否正确
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="answer"></param>
        /// <returns></returns>
        public int CheckForgetPwdAnswer(int userid, string answer)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid),
                new SqlParameter("@Answer", answer)
            };
            string sql = "SELECT ID FROM E_User WHERE ID = @UserID AND ForgetPwdAnswer = @Answer";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql, parms))
            {
                if (dr.Read())
                {
                    return (int)dr["ID"];
                }
                else
                    return -1;
            }
        }

        /// <summary>
        /// 获取忘记密码提示问题列表
        /// </summary>
        /// <returns></returns>
        public IDictionary<int, string> GetForgetPwdQuestionList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Title FROM E_Dict_PwdQuestion";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取忘记密码提示问题
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetForgetPwdQuestionTitleByID(int id)
        {
            string sql = "SELECT Title FROM E_Dict_PwdQuestion WHERE ID = " + id;
            return SqlHelper.ExecuteScalarBySql(sql).ToString();
        }

        /// <summary>
        /// 更新安全信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateFortgetPwdInfo(PwdQuestionUserModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.UserID),
                new SqlParameter("@ForgetPwdQuestionID", model.ID),
                new SqlParameter("@Answer", model.Answer),
            };
            string sql = "UPDATE E_User SET ForgetPwdQuestionID = @ForgetPwdQuestionID, ForgetPwdAnswer = @Answer WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        #endregion

        #region 内训

        /// <summary>
        /// 获取用户项目组ID
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int GetUserProjectID(int userid)
        {
            string sql = "SELECT CurrentCertificateID FROM E_User WHERE ID = " + userid;
            return Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql));
        }

        #endregion

        /// <summary>
        /// 获得技能组列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetSkillGroup()
        {

            string sql = "SELECT -1 as Id,'--请选择--' as SkillGroupName UNION ALL SELECT Id, SkillGroupName from E_SkillGroup";
            return SqlHelper.ExecuteDataSetBySql(sql).Tables[0];
        }
        /// <summary>
        /// 获取用户Email信息
        /// </summary>
        /// <returns></returns>
        public IList<string> GetUserEmail()
        {
            IList<string> lst = new List<string>();
            string sql = "SELECT Email FROM E_UserInfo a inner join dbo.E_User b on a.userid = b.id and a.email <>''";
            UserModel model = new UserModel();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    lst.Add(dr["Email"].ToString());
                }
            }
            return lst;
        }


        /// <summary>
        /// 获得所有没有完成课程的学生邮件信息
        /// </summary>
        /// <returns></returns>
        public IList<SearchStudyLogModel> GetUnfinishUserCourseEmail()
        {
            IList<SearchStudyLogModel> list = new List<SearchStudyLogModel>();
            string sql = "usp_getUnfinishUserCourseEmail";
            SearchStudyLogModel model;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc(sql))
            {
                while (dr.Read())
                {
                    model = new SearchStudyLogModel();
                    model.UserName = dr["UserName"].ToString();
                    model.Email = dr["UserEmail"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    list.Add(model);
                }
            }
            return list;
        }
    }
}
